package com.zzsz.cf.translation.service.impl;

import com.zzsz.cf.translation.BusinessException;
import com.zzsz.cf.translation.entity.ProductCategory;
import com.zzsz.cf.translation.entity.ProductCategoryExtension;
import com.zzsz.cf.translation.mapper.ProductCategoryMapper;
import com.zzsz.cf.translation.mapper.ProductCategoryExtensionMapper;
import com.zzsz.cf.translation.service.ProductCategoryService;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class ProductCategoryServiceImpl implements ProductCategoryService {

    private static final Logger log = LoggerFactory.getLogger(ProductCategoryServiceImpl.class);
    @Autowired
    private ProductCategoryMapper categoryMapper;  // 主分类Mapper

    @Autowired
    private ProductCategoryExtensionMapper extensionMapper;  // 扩展表Mapper

    @Override
    public List<Map<String, Object>> getCategoryListWithExt() {
        // 1. 查询主分类列表（含扩展表关联字段）
        List<ProductCategory> categories = categoryMapper.selectAllCategories();
        if (categories.isEmpty()) {
            return Collections.emptyList();// 无数据时返回空列表
        }

        // 2. 提取主分类ID列表，批量查询扩展表数据（避免N+1查询）
        List<Integer> categoryIds = categories.stream()
                .map(ProductCategory::getCategory_id)
                .collect(Collectors.toList());
        List<ProductCategoryExtension> extensions = extensionMapper.selectByCategoryIds(categoryIds);
        Map<Integer, ProductCategoryExtension> extMap = extensions.stream()
                .collect(Collectors.toMap(ProductCategoryExtension::getCategory_id, ext -> ext, (k1, k2) -> k1)); // 处理重复ID（如有）

        // 3. 转换分类数据为Map结构，初始化子分类列表
        Map<Integer, Map<String, Object>> categoryMap = new HashMap<>();
        for (ProductCategory category : categories) {
            Map<String, Object> item = new HashMap<>(16); // 初始容量优化

            // 主表核心字段（含空值处理）
            item.put("categoryId", category.getCategory_id());

            item.put("categoryName", Optional.ofNullable(category.getCategory_name()).orElse(""));
            log.info("categoryName: {}", item.get("categoryName"));
            item.put("parentId", Optional.ofNullable(category.getParent_id()).orElse(0)); // 父ID默认0（顶级分类）
            item.put("sortOrder", Optional.ofNullable(category.getSort_order()).orElse(0));
            item.put("status", Optional.ofNullable(category.getStatus()).orElse(1)); // 状态默认1（启用）
            item.put("createdAt", category.getCreated_at());
            item.put("updatedAt", category.getUpdated_at());
            item.put("children", new ArrayList<Map<String, Object>>()); // 强制初始化子分类列表
            item.put("description", Optional.ofNullable(category.getDescription()).orElse(""));


            // 扩展表字段（关联扩展信息）
            ProductCategoryExtension ext = extMap.get(category.getCategory_id());
            if (ext != null) {
                item.put("icon", Optional.ofNullable(ext.getIcon()).orElse("")); // 图标默认空字符串
                item.put("extStatus", ext.getStatus());
                item.put("extSortOrder", ext.getSort_order());
            } else {
                item.put("icon", "");
                item.put("extStatus", 1); // 默认状态
                item.put("extSortOrder", 0); // 默认排序
            }

            categoryMap.put(category.getCategory_id(), item);
        }

        // 4. 构建树形结构（父子分类关联）
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> item : categoryMap.values()) {
            Integer parentId = (Integer) item.get("parentId");
            if (parentId == 0) {
                resultList.add(item); // 顶级分类直接加入结果集
            } else {
                Map<String, Object> parentItem = categoryMap.get(parentId);
                if (parentItem != null) {
                    // 将当前分类添加到父分类的children列表
                    ((List<Map<String, Object>>) parentItem.get("children")).add(item);
                } else {
                    // 父分类不存在时，降级为顶级分类（避免数据异常导致分类丢失）
                    resultList.add(item);
                }
            }
        }

        // 5. 按排序权重升序排列（确保展示顺序正确）
        resultList.sort(Comparator.comparingInt(item -> (Integer) item.get("sortOrder")));
        return resultList;
    }

    @Override
    public void addCategory(Map<String, Object> categoryData) {
        // 1. 保存主分类表（修正 parent_id 处理逻辑）
        ProductCategory category = new ProductCategory();
        category.setCategory_name((String) categoryData.get("categoryName"));
        category.setDescription((String) categoryData.get("description"));
        category.setCreated_at(new Date());  // 补充创建时间（若数据库未自动填充）
        category.setUpdated_at(new Date());

        // 修复：顶级分类 parent_id 应设为 NULL（而非 0），避免外键约束冲突
        Integer parentId = (Integer) categoryData.get("parentId");
        category.setParent_id(parentId != null && parentId > 0 ? parentId : null); // 0 或 null 时存为 NULL

        categoryMapper.insert(category);

        // 2. 保存扩展表（代码不变）
        ProductCategoryExtension extension = new ProductCategoryExtension();
        extension.setCategory_id(category.getCategory_id());
        extension.setStatus((Integer) categoryData.get("status"));
        extension.setSort_order((Integer) categoryData.get("sortOrder"));
        extension.setIcon((String) categoryData.get("icon"));
        extensionMapper.insert(extension);
    }

    @Override
    public void updateCategoryStatus(Integer categoryId, Integer status) {
        // 参数校验
        if (categoryId == null || status == null) {
            throw new IllegalArgumentException("分类ID和状态不能为空");
        }
        if (status != 0 && status != 1) {
            throw new IllegalArgumentException("状态值必须为0（禁用）或1（启用）");
        }

        // 更新扩展表状态（需确保扩展表Mapper存在对应方法）
        extensionMapper.updateStatus(categoryId, status);
    }

    @Override
    @Transactional
    public void updateCategory(Map<String, Object> categoryData) {
        // 基本信息更新
        Integer categoryId = Integer.parseInt(categoryData.get("categoryId").toString());
        String categoryName = categoryData.get("categoryName").toString();
        String description = categoryData.get("description") != null ? categoryData.get("description").toString() : null;

        // 更新主表
        categoryMapper.updateCategory(categoryId, categoryName, description);

        // 更新扩展表
        if (categoryData.containsKey("status")) {
            Integer status = Integer.parseInt(categoryData.get("status").toString());
            categoryMapper.updateCategoryStatus(categoryId, status);
        }

        if (categoryData.containsKey("sortOrder")) {
            Integer sortOrder = Integer.parseInt(categoryData.get("sortOrder").toString());
            categoryMapper.updateCategorySortOrder(categoryId, sortOrder);
        }

        if (categoryData.containsKey("icon")) {
            String icon = categoryData.get("icon") != null ? categoryData.get("icon").toString() : null;
            categoryMapper.updateCategoryIcon(categoryId, icon);
        }
    }

    @Override
    @Transactional
    public void deleteCategory(Integer categoryId) {
        // 删除旧代码：检查是否有子分类
        // int childCount = categoryMapper.countChildCategories(categoryId);
        // if (childCount > 0) {
        //     throw new BusinessException("该分类下存在子分类，无法删除");
        // }

        // 1. 查询当前分类下所有子分类ID（递归查询所有层级）
        List<Integer> allCategoryIds = getAllCategoryIds(categoryId);

        // 2. 检查是否有商品使用这些分类
        int productCount = categoryMapper.countProductsByCategoryIds(allCategoryIds);
        if (productCount > 0) {
            throw new BusinessException("所选分类及其子分类下存在商品，无法删除");
        }

        // 3. 级联删除所有相关分类的扩展信息
        extensionMapper.deleteByCategoryIds(allCategoryIds);

        // 4. 级联删除所有分类（包括子分类）
        categoryMapper.deleteByIds(allCategoryIds);
    }

    // 新增：递归获取所有子分类ID（包括当前分类）
    private List<Integer> getAllCategoryIds(Integer categoryId) {
        List<Integer> result = new ArrayList<>();
        result.add(categoryId); // 添加当前分类ID

        // 查询直接子分类
        List<Integer> childIds = categoryMapper.selectChildCategoryIds(categoryId);

        // 递归查询子分类的子分类
        for (Integer childId : childIds) {
            result.addAll(getAllCategoryIds(childId));
        }

        return result;
    }

    @Override
    public void exportCategories(HttpServletResponse response) {
        try {
            // 1. 查询所有分类数据
            List<Map<String, Object>> categories = getCategoryListWithExt();

            // 2. 创建Excel工作簿
            Workbook workbook = WorkbookFactory.create(true);
            Sheet sheet = workbook.createSheet("商品分类");

            // 3. 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {"父分类", "子分类", "备注信息", "状态"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // 4. 填充数据
            int rowNum = 1;
            for (Map<String, Object> category : categories) {
                // 处理顶级分类
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(category.get("categoryName").toString());
                row.createCell(1).setCellValue(""); // 无子分类
                row.createCell(2).setCellValue(category.get("description").toString());
                row.createCell(3).setCellValue((Integer) category.get("status") == 1 ? "启用" : "禁用");

                // 处理子分类
                List<Map<String, Object>> children = (List<Map<String, Object>>) category.get("children");
                for (Map<String, Object> child : children) {
                    Row childRow = sheet.createRow(rowNum++);
                    childRow.createCell(0).setCellValue(category.get("categoryName").toString());
                    childRow.createCell(1).setCellValue(child.get("categoryName").toString());
                    childRow.createCell(2).setCellValue(child.get("description").toString());
                    childRow.createCell(3).setCellValue((Integer) child.get("status") == 1 ? "启用" : "禁用");
                }
            }

            // 5. 设置响应头，下载Excel
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("商品分类列表.xlsx", "UTF-8"));

            // 6. 输出Excel
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();

        } catch (Exception e) {
            throw new BusinessException("导出分类失败: " + e.getMessage());
        }
    }

    @Override
    @Transactional
    public int importCategories(MultipartFile file) {
        if (file.isEmpty()) {
            throw new BusinessException("导入文件不能为空");
        }

        int count = 0;
        try (InputStream inputStream = file.getInputStream()) {
            // 1. 读取Excel文件
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 2. 创建父分类名称到ID的映射
            Map<String, Integer> categoryNameToIdMap = new HashMap<>();
            List<Map<String, Object>> allCategories = getCategoryListWithExt();
            buildCategoryNameMap(allCategories, categoryNameToIdMap);

            // 3. 遍历Excel行，从第二行开始（第一行是表头）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 4. 获取单元格数据
                String parentCategory = getCellValue(row.getCell(0));
                String childCategory = getCellValue(row.getCell(1));
                String description = getCellValue(row.getCell(2));
                String statusStr = getCellValue(row.getCell(3));
                int status = "启用".equals(statusStr) ? 1 : 0;

                // 5. 处理父分类
                if (parentCategory != null && !parentCategory.isEmpty() &&
                        (childCategory == null || childCategory.isEmpty())) {
                    // 仅父分类，无子分类
                    if (!categoryNameToIdMap.containsKey(parentCategory)) {
                        // 创建新的父分类
                        Integer parentId = createCategory(parentCategory, null, description, status);
                        categoryNameToIdMap.put(parentCategory, parentId);
                        count++;
                    }
                }
                // 6. 处理子分类
                else if (parentCategory != null && !parentCategory.isEmpty() &&
                        childCategory != null && !childCategory.isEmpty()) {
                    // 确保父分类存在
                    if (!categoryNameToIdMap.containsKey(parentCategory)) {
                        // 创建新的父分类
                        Integer parentId = createCategory(parentCategory, null, "", 1);
                        categoryNameToIdMap.put(parentCategory, parentId);
                        count++;
                    }

                    // 检查子分类是否已存在
                    String childKey = parentCategory + "|" + childCategory;
                    if (!categoryNameToIdMap.containsKey(childKey)) {
                        // 创建新的子分类
                        Integer parentId = categoryNameToIdMap.get(parentCategory);
                        Integer childId = createCategory(childCategory, parentId, description, status);
                        categoryNameToIdMap.put(childKey, childId);
                        count++;
                    }
                }
            }

            workbook.close();
            return count;

        } catch (Exception e) {
            throw new BusinessException("导入分类失败: " + e.getMessage());
        }
    }

    // 辅助方法：构建分类名称到ID的映射
    private void buildCategoryNameMap(List<Map<String, Object>> categories, Map<String, Integer> map) {
        for (Map<String, Object> category : categories) {
            String name = category.get("categoryName").toString();
            Integer id = (Integer) category.get("categoryId");
            map.put(name, id);

            // 处理子分类，使用"父分类|子分类"作为键
            List<Map<String, Object>> children = (List<Map<String, Object>>) category.get("children");
            for (Map<String, Object> child : children) {
                String childName = child.get("categoryName").toString();
                Integer childId = (Integer) child.get("categoryId");
                map.put(name + "|" + childName, childId);
            }
        }
    }

    // 辅助方法：创建分类
    private Integer createCategory(String name, Integer parentId, String description, int status) {
        // 创建主分类
        ProductCategory category = new ProductCategory();
        category.setCategory_name(name);
        category.setParent_id(parentId);
        category.setDescription(description);
        category.setCreated_at(new Date());
        category.setUpdated_at(new Date());
        categoryMapper.insert(category);

        // 创建分类扩展信息
        ProductCategoryExtension extension = new ProductCategoryExtension();
        extension.setCategory_id(category.getCategory_id());
        extension.setStatus(status);
        extension.setSort_order(0);
        extension.setCreated_at(new Date());
        extension.setUpdated_at(new Date());
        extensionMapper.insert(extension);

        return category.getCategory_id();
    }

    // 辅助方法：获取单元格值
    private String getCellValue(Cell cell) {
        if (cell == null) return "";

        String value = "";
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().toString();
                } else {
                    value = String.valueOf((long) cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                value = "";
        }
        return value.trim();
    }

    // 修改getCategoryStatistics方法中的类型转换
    @Override
    public Map<String, Object> getCategoryStatistics() {
        Map<String, Object> result = new HashMap<>();

        // 查询父分类及其子分类数量
        List<Map<String, Object>> parentCategories = categoryMapper.selectParentCategoriesWithChildCount();

        List<String> parentNames = new ArrayList<>();
        List<Long> childCounts = new ArrayList<>();
        Map<Long, List<Map<String, Object>>> childDetails = new HashMap<>();

        // 计算总子分类数量，用于百分比计算
        long totalChildCount = 0;
        for (Map<String, Object> parent : parentCategories) {
            Long count = ((Number) parent.get("childCount")).longValue();
            totalChildCount += count;
        }

        for (Map<String, Object> parent : parentCategories) {
            Long parentId = ((Number) parent.get("categoryId")).longValue();
            String parentName = (String) parent.get("categoryName");
            Long count = ((Number) parent.get("childCount")).longValue();

            parentNames.add(parentName);
            childCounts.add(count);

            // 查询子分类详情
            List<Map<String, Object>> children = categoryMapper.selectChildCategories(parentId.intValue());
            childDetails.put(parentId, children);
        }

        result.put("parentNames", parentNames);
        result.put("childCounts", childCounts);
        result.put("childDetails", childDetails);
        result.put("totalChildCount", totalChildCount);  // 添加总数量

        return result;
    }
}